﻿/**************************************************************************
创建日期:	2017/5/12 0:16:39     
作	  者:	张存
邮 	  箱:	zhangcunliang@126.com
创建时间:	
Copyright (c)  zhcun.cn
描	述：
记	录：  
    2019.8.27 增加可用字符串数据库类型 构造函数3
    2020.1.13 新增或更新时忽略主键的bug
    2020.5.16 更新增加 set 表达式的方式进行更新，如 Age = Age + 1 (字符串录入)
    2020.6.8  统计等函数查询，增加 sqlBuiler 的合并查询
    2020.9.15 当非空DateTime为MinValue,不对字段进行做新增
    2020.10.10 IsNotField 不会加入属性字段中,当赋值后会引发异常(如:Tag)
    2020.11.12 增加 InsertBuik 批量增加的实现 
    2020.1.23  存储过程增加动态输入参数 Dictionary<string,object>
    2021.5.13  增加按存储过程名执行存储过程
    2021.12.10 解决执行存储过程时的无限递归bug
    2022.3.19  自增长始终跳过2021.1.20 是个bug，当使用实体直接更新时不会赋值


***************************************************************************/
using ASmile.ORM.BuildSQLText;
using ASmile.ORM.ADO;
using ASmile.ORM.Entitys;
using ASmile.ORM.ExpressionAnaly;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Linq.Expressions;
using System.Text;

namespace ASmile.ORM.Cores
{
    /// <summary>
    /// 数据库操作核心上下文
    /// </summary>
    public class DBContext
    {
        /// <summary>
        /// 数据库类型字符串 转换为枚举类型,如果为空或非法字符，则返回 EmDbType.SQLServer
        /// </summary>
        public static EmDbType GetDbType(string dbTypeStr)
        {
            EmDbType dbType;
            if (dbTypeStr == null) return EmDbType.SQLServer;
            //SQLServer = 1,SQLite = 2,Oracle = 3,MySQL = 4,OleDb = 5
            dbTypeStr = dbTypeStr.ToLower();
            switch (dbTypeStr)
            {
                case "sqlite":
                    dbType = EmDbType.SQLite;
                    break;
                case "oracle":
                    dbType = EmDbType.Oracle;
                    break;
                case "mysql":
                    dbType = EmDbType.MySQL;
                    break;
                default:
                    dbType = EmDbType.SQLServer;
                    break;
            }
            return dbType;
        }
        /// <summary>
        /// 默认以SqlServer 连接数据库
        /// </summary>
        public DBContext(string connStr)
            : this(string.Empty, connStr)
        { }

        public DBContext(string dbType, string connStr)
            : this(GetDbType(dbType), connStr)
        { }

        public DBContext(EmDbType dbType, string connStr)
        {
            DbHelper = ContextFactory.Instance.CreateDbHelper(dbType, connStr);
            BuildSql = ContextFactory.Instance.CreateBuildSQL(dbType);
            EntityAnaly = ContextFactory.Instance.CreateEntityAnaly();
            ExprAnaly = ContextFactory.Instance.CreateExpressionAnaly();
            DBType = dbType;
        }

        /// <summary>
        /// 扩展where条件注入的SqlBuilder的参数名键
        /// </summary>
        const string WHERE_EX_KEY = "EX";

        private BuildSQLBase BuildSql { get; }

        internal EntityAnaly EntityAnaly { get; }

        private IExpressionAnaly ExprAnaly { get; }

        private EmDbType DBType { get; }

        /// <summary>
        /// 数据库操作对象
        /// </summary>
        public IDBHelper DbHelper { get; }
        /// <summary>
        /// 创建扩展where生成器对象
        /// </summary>
        ISqlBuilder CreateExWhereBuilder()
        {
            return CreateSqlBuilder(WHERE_EX_KEY);
        }
        /// <summary>
        /// 获取属性值，指定是否忽略自增长主键
        /// </summary>
        protected Dictionary<string, object> GetEntityValue<TEntity>(TEntity entity, bool ignorePK, bool ignoreIdentity) where TEntity : EntityBase, new()
        {
            string[] changedFields = entity.GetChangedFields();
            Dictionary<string, object> fieldValues = new Dictionary<string, object>();
            EntityAttribute entityAttr;
            foreach (var fieldName in changedFields)
            {
                object v = EntityAnaly.GetPropertyValue(entity, fieldName);

                entityAttr = EntityAnaly.GetAttribute<TEntity>(fieldName);
                // IsNotField 不会加入属性自定中 2020.10.10 
                if (!(entityAttr?.IsNotField == false)) continue;
                if (ignoreIdentity && entityAttr?.IsIdentity == true) continue;

                if (ignorePK && (entityAttr?.IsPrimaryKey == true)) continue;

                if (v is DateTime dt)
                {
                    if (dt == DateTime.MinValue) continue;
                }
                fieldValues.Add(fieldName, v);
            }
            return fieldValues;
        }
        /// <summary>
        /// 获取实体对象值，不忽略主键值与自增长
        /// </summary>
        protected Dictionary<string, object> GetEntityValue<TEntity>(TEntity entity) where TEntity : EntityBase, new()
        {
            return GetEntityValue(entity, false, false);
        }
        /// <summary>
        /// 创建查询条件对象，分页、排序；
        /// </summary>
        public QueryCondition<TEntity> CreateQuery<TEntity>() where TEntity : class
        {
            return new QueryCondition<TEntity>();
        }
        /// <summary>
        /// 创建执行的条件对象，不包含分页、排序
        /// </summary>
        public ExecCondition<TEntity> CreateWhere<TEntity>() where TEntity : EntityBase, new()
        {
            return new ExecCondition<TEntity>();
        }
        /// <summary>
        /// 创建sql生成器
        /// </summary>
        public virtual ISqlBuilder CreateSqlBuilder()
        {
            return BuildSql.CreateBuildResult();
        }
        /// <summary>
        /// 创建指定参数名称前缀的sql生成器（解决参数名称重复）
        /// </summary>
        public virtual ISqlBuilder CreateSqlBuilder(string paramKey)
        {
            return BuildSql.CreateBuildResult(paramKey);
        }
        /// <summary>
        /// 创建基于实体形参的where条件的sqlBuider对象（共通封装）
        /// </summary>
        protected virtual ISqlBuilder CreateQuerySqlBuilder<TRet>() where TRet : class, new()
        {
            string tbName = EntityAnaly.GetTableName<TRet>();
            string[] selectFields = EntityAnaly.GetAllFields<TRet>();
            ISqlBuilder sqlBuilder = BuildSql.BuildQuery(tbName, selectFields);
            return sqlBuilder;
        }
        /// <summary>
        /// 开启事务，并指定事务级别
        /// </summary>
        /// <param name="level">级别</param>
        public void TransStart(IsolationLevel level)
        {
            DbHelper.TransStart(level);
        }
        /// <summary>
        /// 事务开始
        /// </summary>
        public void TransStart()
        {
            DbHelper.TransStart();
        }
        /// <summary>
        /// 事务提交
        /// </summary>
        public void TransCommit()
        {
            DbHelper.TransCommit();
        }
        /// <summary>
        /// 事务回滚
        /// </summary>
        public void TransRollback()
        {
            DbHelper.TransRollback();
        }
        /// <summary>
        /// 当使用sqlbuilder（所有实体类执行的Sql都会触发）进行 更新、删除、查询、并指定具体实体类型时在执行前触发，
        /// 用于数据过滤的统一处理；
        /// 需要子类重写处理过程
        /// </summary>
        protected virtual void BeforeExecSqlBuilder<TEntity>(ISqlBuilder sqlBuilder, EmDbOperation opType) where TEntity : class
        { }
        /// <summary>
        /// 根据sql建造起执行sql语句
        /// </summary>
        public virtual ExecResult ExecSql(ISqlBuilder sqlBuilder)
        {
            DbParameter[] dbParam = DbHelper.ConvertDbParameter(sqlBuilder.DbParam);
            int count = DbHelper.ExecNonQuery(sqlBuilder.SQLText, dbParam);
            var result = new ExecResult(count, sqlBuilder);
            return result;
        }
        /// <summary>
        /// 根据sql建造起执行sql语句，加上执行sql where文本
        /// </summary>
        protected virtual ExecResult ExecSql(ISqlBuilder sqlBuilder, string sqlWhere)
        {
            if (!string.IsNullOrWhiteSpace(sqlWhere))
            {
                sqlBuilder.AddSQLText(" where {0}", sqlWhere);
            }
            return ExecSql(sqlBuilder);
        }
        /// <summary>
        /// 新增一条记录
        /// </summary>
        public virtual ExecResult Insert<TEntity>(TEntity entity) where TEntity : EntityBase, new()
        {
            string tbName = EntityAnaly.GetTableName<TEntity>();
            Dictionary<string, object> entityValue = GetEntityValue(entity, false, true);
            ISqlBuilder sqlBuilder = BuildSql.BuildInsert(tbName, entityValue);
            entity.ClearChangedState();
            return ExecSql(sqlBuilder, null);
        }
        /// <summary>
        /// 进行批量插入，oracle 与 sqlite 使用事务插入，sqlserver 与 mysql 做了优化处理
        /// </summary>
        public virtual int InsertBulk<TEntity>(List<TEntity> dataList)
            where TEntity : EntityBase, new()
        {
            if (DBType == EmDbType.SQLServer || DBType == EmDbType.MySQL)
            {
                var allFields = EntityAnaly.GetAllFields<TEntity>();
                string tbName = EntityAnaly.GetTableName<TEntity>();
                var rowCount = DbHelper.BulkInsert(tbName, allFields, dataList);
                return rowCount;
            }
            else
            {
                try
                {
                    TransStart(IsolationLevel.ReadUncommitted);
                    foreach (var item in dataList)
                    {
                        Insert(item);
                    }
                    TransCommit();
                }

                catch (Exception ex)
                {
                    TransRollback();
                    throw ex;
                }
                return dataList.Count;
            }
        }
        /// <summary>
        /// 实体对象更新，主键必填
        /// </summary>
        public virtual ExecResult Update<TEntity>(TEntity entity) where TEntity : EntityBase, new()
        {
            return UpdateBase(entity, null);
        }
        /// <summary>
        /// lamda表达式更新
        /// </summary>
        public virtual ExecResult Update<TEntity>(TEntity entity, Expression<Func<TEntity, bool>> lamda) where TEntity : EntityBase, new()
        {
            return
            UpdateBase(entity, (sqlBuilder) =>
            {
                ExprAnaly.AnalyWhereExpression<TEntity>(lamda, BuildSql.FormatName, sqlBuilder.AddParam);
                string exprSql = ExprAnaly.GetValue();
                return exprSql;
            });
        }
        /// <summary>
        /// where对象条件更新
        /// </summary>
        public virtual ExecResult Update<TEntity>(TEntity entity, ICondition condition) where TEntity : EntityBase, new()
        {
            return
            UpdateBase(entity, (sqlBuilder) =>
            {
                string exprSql = WhereJoin<TEntity>(condition.WhereExpressions, sqlBuilder);
                return exprSql;
            });
        }
        /// <summary>
        /// 更新的老大
        /// </summary>
        protected virtual ExecResult UpdateBase<TEntity>(TEntity entity, Func<ISqlBuilder, string> whereFun) where TEntity : EntityBase, new()
        {
            string tbName = EntityAnaly.GetTableName<TEntity>();
            string exprSql = null;
            ISqlBuilder sqlBuilder;
            ISqlBuilder whereSql = CreateExWhereBuilder();
            if (whereFun == null)
            {
                //按主键更新
                string[] pkNames = EntityAnaly.GetPrimaryKey<TEntity>();
                foreach (var item in pkNames)
                {
                    entity.SetFieldChanged(item);
                }
                Dictionary<string, object> entityValue = GetEntityValue(entity);
                sqlBuilder = BuildSql.BuildUpdate(tbName, entityValue, pkNames);
            }
            else
            {
                //按条件更新
                Dictionary<string, object> entityValue = GetEntityValue(entity, true, true);
                sqlBuilder = BuildSql.BuildUpdate(tbName, entityValue);
                exprSql = whereFun(whereSql);
                whereSql.AddSQLText(exprSql);
            }
            entity.ClearChangedState();
            BeforeExecSqlBuilder<TEntity>(whereSql, EmDbOperation.Update);
            if (string.IsNullOrWhiteSpace(exprSql) && whereSql.SQLTextLength > 2)
            {
                //前部分是主键条件(id=@ID) 
                sqlBuilder.AddSQLText(" and ({0})", whereSql.SQLText);
            }
            else
            {
                exprSql = whereSql.SQLText;
            }
            sqlBuilder.AppendDbParam(whereSql.DbParam);
            return ExecSql(sqlBuilder, exprSql);
        }
        /// <summary>
        /// set 表达式的方式进行更新，如 Age = Age + 1,字符串录入 
        /// </summary>
        protected virtual ExecResult UpdateByExprBase<TEntity>(Func<ISqlBuilder, string> whereFun, params string[] setExpr)
            where TEntity : EntityBase, new()
        {
            string tbName = EntityAnaly.GetTableName<TEntity>();
            ISqlBuilder sqlBuilder = CreateSqlBuilder();
            ISqlBuilder whereSql = CreateExWhereBuilder();
            string setExprSql = string.Join(",", setExpr);
            sqlBuilder.AddSQLText("Update {0} set {1} ", tbName, setExprSql);
            string exprSql = whereFun(whereSql);
            whereSql.AddSQLText(exprSql);
            BeforeExecSqlBuilder<TEntity>(whereSql, EmDbOperation.Update);
            if (string.IsNullOrWhiteSpace(exprSql) && whereSql.SQLTextLength > 2)
            {
                //前部分是主键条件(id=@ID) 
                sqlBuilder.AddSQLText(" {0}", whereSql.SQLText);
            }
            else
            {
                exprSql = whereSql.SQLText;
            }
            sqlBuilder.AppendDbParam(whereSql.DbParam);
            return ExecSql(sqlBuilder, exprSql);
        }
        /// <summary>
        /// set 表达式的方式进行更新，如 Age = Age + 1,字符串录入的lamda表达式
        /// </summary>
        public ExecResult UpdateByExpr<TEntity>(Expression<Func<TEntity, bool>> lamda, params string[] setExpr)
            where TEntity : EntityBase, new()
        {
            return
            UpdateByExprBase<TEntity>((sqlBuilder) =>
            {
                ExprAnaly.AnalyWhereExpression(lamda, BuildSql.FormatName, sqlBuilder.AddParam);
                string exprSql = ExprAnaly.GetValue();
                return exprSql;
            }, setExpr);
        }
        /// <summary>
        /// set where的方式进行更新
        /// </summary>
        public virtual ExecResult UpdateByExpr<TEntity>(ICondition condition, params string[] setExpr)
            where TEntity : EntityBase, new()
        {
            return
            UpdateByExprBase<TEntity>((sqlBuilder) =>
            {
                string exprSql = WhereJoin<TEntity>(condition.WhereExpressions, sqlBuilder);
                return exprSql;
            }, setExpr);
        }
        /// <summary>
        /// 根据主键删除一条数据
        /// </summary>
        public virtual ExecResult Delete<TEntity>(TEntity entity) where TEntity : EntityBase, new()
        {
            //TODO: 可考虑 将该查询统一调用DeleteBase 2018.4.26 
            string tbName = EntityAnaly.GetTableName<TEntity>();
            string[] pkNames = EntityAnaly.GetPrimaryKey<TEntity>();
            entity.SetFieldChanged(pkNames[0]);
            Dictionary<string, object> entityValue = GetEntityValue(entity);
            ISqlBuilder sqlBuilder = BuildSql.BuildDelete(tbName, entityValue, pkNames);
            ISqlBuilder whereBuilder = CreateExWhereBuilder();
            BeforeExecSqlBuilder<TEntity>(whereBuilder, EmDbOperation.Delete);
            if (whereBuilder.SQLTextLength > 2)
            {
                sqlBuilder.AddSQLText(" and ({0})", whereBuilder.SQLText);
                sqlBuilder.AppendDbParam(whereBuilder.DbParam);
            }
            return ExecSql(sqlBuilder, null);
        }
        /// <summary>
        /// 使用表达式删除的老大
        /// </summary>
        protected virtual ExecResult DeleteBase<TEntity>(Func<ISqlBuilder, string> whereFun) where TEntity : EntityBase, new()
        {
            string tbName = EntityAnaly.GetTableName<TEntity>();
            ISqlBuilder sqlBuilder = BuildSql.BuildDelete(tbName, null, null);
            ISqlBuilder whereBuilder = CreateExWhereBuilder();
            string exprSql = whereFun(whereBuilder);
            whereBuilder.AddSQLText(exprSql);
            BeforeExecSqlBuilder<TEntity>(whereBuilder, EmDbOperation.Delete);
            exprSql = whereBuilder.SQLText;
            sqlBuilder.AppendDbParam(whereBuilder.DbParam);
            return ExecSql(sqlBuilder, exprSql);
        }
        /// <summary>
        /// 根据一个基本的表达式进行删除操作
        /// </summary>
        /// <typeparam name="TEntity">实体对象类型</typeparam>
        /// <param name="lamda">表达式</param>
        /// <returns>返回执行结果对象</returns>
        public virtual ExecResult Delete<TEntity>(Expression<Func<TEntity, bool>> lamda) where TEntity : EntityBase, new()
        {
            return
            DeleteBase<TEntity>((sqlBuilder) =>
            {
                ExprAnaly.AnalyWhereExpression<TEntity>(lamda, BuildSql.FormatName, sqlBuilder.AddParam);
                string exprSql = ExprAnaly.GetValue();
                return exprSql;
            });
        }
        /// <summary>
        /// 使用执行条件对象,进行删除表达式
        /// </summary>
        /// <typeparam name="TEntity">实体对象类型</typeparam>
        /// <param name="condition">执行条件对象</param>
        /// <returns>返回执行结果对象</returns>
        public virtual ExecResult Delete<TEntity>(ICondition condition) where TEntity : EntityBase, new()
        {
            return
           DeleteBase<TEntity>((sqlBuilder) =>
           {
               string exprSql = WhereJoin<TEntity>(condition.WhereExpressions, sqlBuilder);
               return exprSql;
           });
        }
        /// <summary>
        /// 解析contition中的所有表达式,获取排序sql文本
        /// </summary>
        string GetOrderSQL<TEntity>(IQueryCondition query) where TEntity : class
        {
            if (query == null)
            {
                return null;
            }
            if (query.OrderExpressions == null || query.OrderExpressions.Count == 0)
            {
                if (query.PageSize > 0 && query.PageNo > 0)
                {
                    string orderField = string.Join(",", EntityAnaly.GetPrimaryKey<TEntity>());
                    return orderField;
                }
                return null;
            }
            StringBuilder orderSql = new StringBuilder();
            foreach (Expression expr in query.OrderExpressions.Keys)
            {
                Expression<Func<TEntity, object>> selector = (Expression<Func<TEntity, object>>)expr;
                ExprAnaly.AnalySelectorExpression(selector, BuildSql.FormatName);
                string orderField = ExprAnaly.GetValue();
                orderSql.AppendFormat("{0} {1},", orderField, query.OrderExpressions[expr]);
            }
            orderSql.Remove(orderSql.Length - 1, 1);
            return orderSql.ToString();
        }
        /// <summary>
        /// 将多个表达式进行合并所有sqlBuilder生成参数,再返回拼接完整的sql文
        /// </summary>
        string WhereJoin<TEntity>(Dictionary<Expression, EmWhereJoin> JoinList, ISqlBuilder sqlBuilder)
        {
            if (JoinList == null) return string.Empty;
            StringBuilder sqlWhere = new StringBuilder();
            bool isFirst = true;
            string analyResutl;
            foreach (Expression expr in JoinList.Keys)
            {
                Expression<Func<TEntity, bool>> ex = (Expression<Func<TEntity, bool>>)expr;
                ExprAnaly.AnalyWhereExpression<TEntity>(ex, BuildSql.FormatName, sqlBuilder.AddParam);
                if (!isFirst)
                {
                    sqlWhere.AppendFormat(" {0} ", JoinList[expr].ToString());
                }
                analyResutl = ExprAnaly.GetValue();
                sqlWhere.Append(analyResutl);
                isFirst = false;
            }
            return sqlWhere.ToString();
        }
        /// <summary>
        /// 将两个sqlBuilder合并,返回完整的sql文本，mainSqlBuilder 增加对应的参数
        /// </summary>
        /// <param name="exprSql">原表达式sql</param>
        /// <param name="mainSqlBuilder">主sqlBuilder</param>
        /// <param name="joinSqlBuilder">要合并的sqlBuilder</param>
        /// <returns>返回sql</returns>
        string MergeSqlBuilder(string exprSql, ISqlBuilder mainSqlBuilder, ISqlBuilder joinSqlBuilder)
        {
            if (joinSqlBuilder != null && joinSqlBuilder.SQLText.Length > 2)
            {
                if (!string.IsNullOrWhiteSpace(exprSql))
                {
                    exprSql = string.Format("{0} and ({1})", exprSql, joinSqlBuilder.SQLText);
                }
                else
                {
                    exprSql = joinSqlBuilder.SQLText;
                }
                mainSqlBuilder.AppendDbParam(joinSqlBuilder.DbParam);
            }
            return exprSql;
        }
        /// <summary>
        /// 根据一个sqlBuilder来构造一个完整的查询
        /// </summary>
        /// <param name="sqlBuilder">可实现参数化查询的sql文本构造器</param>
        public virtual QueryResult Query(ISqlBuilder sqlBuilder)
        {
            var result = new QueryResult(DbHelper, sqlBuilder);
            return result;
        }
        /// <summary>
        /// 根据一个sqlBuilder 来构造一个完整的查询，用于获取首行首列值
        /// </summary>
        /// <param name="sqlBuilder">可实现参数化查询的sql文本构造器</param>        
        public virtual ScalarResult QueryScalar(ISqlBuilder sqlBuilder)
        {
            var result = new ScalarResult(DbHelper, sqlBuilder);
            return result;
        }


        /**********准备作废的*********
        /// <summary>
        /// 根据一个sqlBuilder来构造where条件的查询
        /// </summary>
        /// <param name="whereSqlBuilder">可实现参数化查询的sql文本构造器, 不包含 where</param>
        public virtual QueryResult<TEntity> Query<TEntity>(ISqlBuilder whereSqlBuilder) where TEntity : class, new()
        {
            var sqlBuilder = CreateQuerySqlBuilder<TEntity>();
            BeforeExecSqlBuilder<TEntity>(whereSqlBuilder, EmDbOperation.Read);
            if (whereSqlBuilder != null && whereSqlBuilder.SQLText.Length > 2)
            {
                sqlBuilder.AddSQLText(" where ");
                sqlBuilder.AddSQLText(whereSqlBuilder.SQLText);
                sqlBuilder.AppendDbParam(whereSqlBuilder.DbParam);
            }
            var result = new QueryResult<TEntity>(DbHelper, sqlBuilder);
            //result.ToList<TEntity>();
            return result;
        }
        /// <summary>
        /// 使用表达式进行普通查询
        /// </summary>
        /// <typeparam name="TEntity">实体对象类型</typeparam>
        /// <param name="lamda">表达式</param>
        public virtual QueryResult<TEntity> Query<TEntity>(Expression<Func<TEntity, bool>> lamda) where TEntity : class, new()
        {
            ISqlBuilder whereBuilder = CreateSqlBuilder();
            ExprAnaly.AnalyWhereExpression(lamda, BuildSql.FormatName, whereBuilder.AddParam);

            string exprSql = ExprAnaly.GetValue();
            whereBuilder.AddSQLText(exprSql);
            return Query<TEntity>(null, whereBuilder);
        }
        /// <summary>
        /// 根据查询对象进行读取数据库，可返回DataTable、List 
        /// </summary>
        public virtual QueryResult<TEntity> Query<TEntity>(IQueryCondition condition) where TEntity : class, new()
        {
            return Query<TEntity>(condition, null);
        }
        /// <summary>
        /// 使用条件对象来进行复杂查询
        /// </summary>
        public virtual QueryResult<TEntity> Query<TEntity>(IQueryCondition condition, ISqlBuilder joinWhereBuilder) where TEntity : class, new()
        {
            if (joinWhereBuilder == null) joinWhereBuilder = CreateExWhereBuilder();
            ISqlBuilder sqlBuilder;
            string tbName = EntityAnaly.GetTableName<TEntity>();
            string[] selectFields = EntityAnaly.GetAllFields<TEntity>();

            if (condition != null && condition.PageNo > 0 && condition.PageSize > 0)
            {   //分页
                sqlBuilder = BuildSql.BuildQueryPager(tbName, selectFields, condition.PageNo, condition.PageSize);
                string exprSql = WhereJoin<TEntity>(condition.WhereExpressions, sqlBuilder); //表达式的 where条件                
                BeforeExecSqlBuilder<TEntity>(joinWhereBuilder, EmDbOperation.Read);
                exprSql = MergeSqlBuilder(exprSql, sqlBuilder, joinWhereBuilder);
                if (string.IsNullOrEmpty(exprSql)) exprSql = "1=1";
                string pagerSql = sqlBuilder.SQLText;
                sqlBuilder.ClearSQL();
                string orderSql = GetOrderSQL<TEntity>(condition);
                sqlBuilder.AddSQLText(pagerSql, exprSql, orderSql);
                //查询分页前的数量
                ISqlBuilder sqlCountBuilder = BuildSql.BuildCountFunction(tbName);
                exprSql = WhereJoin<TEntity>(condition.WhereExpressions, sqlCountBuilder); //表达式的 where条件                
                exprSql = MergeSqlBuilder(exprSql, sqlCountBuilder, joinWhereBuilder);
                if (!string.IsNullOrEmpty(exprSql))
                {
                    sqlCountBuilder.AddSQLText(" where ");
                    sqlCountBuilder.AddSQLText(exprSql);
                }
                var scalarResult = new ScalarResult(DbHelper, sqlCountBuilder);
                ((QueryCondition<TEntity>)condition).Total = scalarResult.ToObject<int>();
            }
            else
            {   //非分页
                sqlBuilder = BuildSql.BuildQuery(tbName, selectFields);
                string exprSql = null;
                if (condition != null)
                {
                    exprSql = WhereJoin<TEntity>(condition.WhereExpressions, sqlBuilder);
                }
                BeforeExecSqlBuilder<TEntity>(joinWhereBuilder, EmDbOperation.Read);
                exprSql = MergeSqlBuilder(exprSql, sqlBuilder, joinWhereBuilder);
                if (!string.IsNullOrEmpty(exprSql))
                {
                    sqlBuilder.AddSQLText(" where ");
                    sqlBuilder.AddSQLText(exprSql);
                }
                string orderSql = GetOrderSQL<TEntity>(condition);
                if (!string.IsNullOrEmpty(orderSql))
                {
                    sqlBuilder.AddSQLText(" Order By {0}", orderSql);
                }
            }
            var result = new QueryResult<TEntity>(DbHelper, sqlBuilder);
            result.TableName = tbName;
            return result;
        }
        */


        /// <summary>
        /// 使用条件对象来进行复杂查询，表明与结果可不相同
        /// </summary>
        public virtual QueryResult<TRet> Query<TTable, TRet>(QueryCondition<TTable> condition, ISqlBuilder joinWhereBuilder = null)
            where TTable : class, new()
            where TRet : class, new()
        {
            if (joinWhereBuilder == null) joinWhereBuilder = CreateExWhereBuilder();
            ISqlBuilder sqlBuilder;
            string tbName = EntityAnaly.GetTableName<TTable>();
            string[] selectFields = EntityAnaly.GetAllFields<TRet>();

            if (condition != null && condition.PageNo > 0 && condition.PageSize > 0)
            {   //分页
                sqlBuilder = BuildSql.BuildQueryPager(tbName, selectFields, condition.PageNo, condition.PageSize);
                string exprSql = WhereJoin<TTable>(condition.WhereExpressions, sqlBuilder); //表达式的 where条件                
                BeforeExecSqlBuilder<TTable>(joinWhereBuilder, EmDbOperation.Read);
                exprSql = MergeSqlBuilder(exprSql, sqlBuilder, joinWhereBuilder);
                if (string.IsNullOrEmpty(exprSql)) exprSql = "1=1";
                string pagerSql = sqlBuilder.SQLText;
                sqlBuilder.ClearSQL();
                string orderSql = GetOrderSQL<TTable>(condition);
                sqlBuilder.AddSQLText(pagerSql, exprSql, orderSql);
                //查询分页前的数量
                ISqlBuilder sqlCountBuilder = BuildSql.BuildCountFunction(tbName);
                exprSql = WhereJoin<TTable>(condition.WhereExpressions, sqlCountBuilder); //表达式的 where条件                
                exprSql = MergeSqlBuilder(exprSql, sqlCountBuilder, joinWhereBuilder);
                if (!string.IsNullOrEmpty(exprSql))
                {
                    sqlCountBuilder.AddSQLText(" where ");
                    sqlCountBuilder.AddSQLText(exprSql);
                }
                var scalarResult = new ScalarResult(DbHelper, sqlCountBuilder);
                condition.Total = scalarResult.ToObject<int>();
            }
            else
            {   //非分页
                sqlBuilder = BuildSql.BuildQuery(tbName, selectFields);
                string exprSql = null;
                if (condition != null)
                {
                    exprSql = WhereJoin<TTable>(condition.WhereExpressions, sqlBuilder);
                }
                BeforeExecSqlBuilder<TTable>(joinWhereBuilder, EmDbOperation.Read);
                exprSql = MergeSqlBuilder(exprSql, sqlBuilder, joinWhereBuilder);
                if (!string.IsNullOrEmpty(exprSql))
                {
                    sqlBuilder.AddSQLText(" where ");
                    sqlBuilder.AddSQLText(exprSql);
                }
                string orderSql = GetOrderSQL<TTable>(condition);
                if (!string.IsNullOrEmpty(orderSql))
                {
                    sqlBuilder.AddSQLText(" Order By {0}", orderSql);
                }
            }
            var result = new QueryResult<TRet>(DbHelper, sqlBuilder);
            result.TableName = tbName;
            return result;
        }
        /// <summary>
        /// 使用条件查询，查询表与结果相同
        /// </summary>
        public virtual QueryResult<TTable> Query<TTable>(QueryCondition<TTable> condition, ISqlBuilder joinWhereBuilder = null)
            where TTable : class, new()
        {
            //condition.OrderBy(s => s.GetType() == null);
            return Query<TTable, TTable>(condition, joinWhereBuilder);
        }
        /// <summary>
        /// 使用条件查询，查询表与结果相同
        /// </summary>
        public virtual QueryResult<TRet> Query<TTable, TRet>(Expression<Func<TTable, bool>> lamda)
            where TTable : class, new()
            where TRet : class, new()
        {
            ISqlBuilder whereBuilder = CreateSqlBuilder();
            ExprAnaly.AnalyWhereExpression(lamda, BuildSql.FormatName, whereBuilder.AddParam);

            string exprSql = ExprAnaly.GetValue();
            whereBuilder.AddSQLText(exprSql);
            return Query<TTable, TRet>(null, whereBuilder);
        }
        /// <summary>
        /// 使用条件查询，查询表与结果相同
        /// </summary>
        public virtual QueryResult<TTable> Query<TTable>(Expression<Func<TTable, bool>> lamda)
            where TTable : class, new()
        {
            return Query<TTable, TTable>(lamda);
        }
        /// <summary>
        /// 根据一个sqlBuilder来构造where条件的查询
        /// </summary>
        /// <param name="whereSqlBuilder">可实现参数化查询的sql文本构造器, 不包含 where</param>
        public virtual QueryResult<TRet> Query<TTable, TRet>(ISqlBuilder whereSqlBuilder)
            where TTable : class
            where TRet : class, new()
        {
            var sqlBuilder = CreateQuerySqlBuilder<TRet>();
            BeforeExecSqlBuilder<TTable>(whereSqlBuilder, EmDbOperation.Read);
            if (whereSqlBuilder != null && whereSqlBuilder.SQLText.Length > 2)
            {
                sqlBuilder.AddSQLText(" where ");
                sqlBuilder.AddSQLText(whereSqlBuilder.SQLText);
                sqlBuilder.AppendDbParam(whereSqlBuilder.DbParam);
            }
            var result = new QueryResult<TRet>(DbHelper, sqlBuilder);
            return result;
        }
        /// <summary>
        /// 根据一个sqlBuilder来构造where条件的查询
        /// </summary>
        /// <param name="whereSqlBuilder">可实现参数化查询的sql文本构造器, 不包含 where</param>
        public virtual QueryResult<TTable> Query<TTable>(ISqlBuilder whereSqlBuilder) where TTable : class, new()
        {
            return Query<TTable, TTable>(whereSqlBuilder);
        }

        public QueryResult<TRet> Query<TTable, TRet>(Action<QueryCondition<TTable>> QueryHandle)
            where TTable : class, new()
            where TRet : class, new()
        {
            var query = CreateQuery<TTable>();
            QueryHandle?.Invoke(query);
            return Query<TTable, TRet>(query);
        }

        public QueryResult<TRet> Query<TRet>(Action<QueryCondition<TRet>> QueryHandle) where TRet : class, new()
        {
            var query = CreateQuery<TRet>();
            QueryHandle?.Invoke(query);
            return Query<TRet, TRet>(query);
        }

        public List<TRet> QueryList<TTable, TRet>(Action<QueryCondition<TTable>> QueryHandle, out int total)
            where TTable : class, new()
            where TRet : class, new()
        {
            var query = CreateQuery<TTable>();
            QueryHandle?.Invoke(query);
            var ret = Query<TTable, TRet>(query);
            total = query.Total;
            return ret.ToList();
        }

        public List<TRet> QueryList<TRet>(Action<QueryCondition<TRet>> QueryHandle, out int total) where TRet : class, new()
        {
            return QueryList<TRet, TRet>(QueryHandle, out total);
        }

        public List<TRet> QueryList<TTable, TRet>(Action<QueryCondition<TTable>> QueryHandle)
            where TTable : class, new()
            where TRet : class, new()
        {
            return QueryList<TTable, TRet>(QueryHandle, out _);
        }

        public List<TRet> QueryList<TRet>(Action<QueryCondition<TRet>> QueryHandle) where TRet : class, new()
        {
            return QueryList<TRet, TRet>(QueryHandle, out _);
        }

        /// <summary>
        /// 查询首行首列的基本方法,其它方法共通,(使用自定义的sql建造方法:GetExprSql)
        /// </summary>
        ScalarResult QueryFunction<TEntity>(
            Expression<Func<TEntity, object>> selector,
            Func<ISqlBuilder, string> GetExprSql,
            Func<string, string, ISqlBuilder> BuildFunction,
            ISqlBuilder joinWhereBuilder = null) where TEntity : class, new()
        {
            string tbName = EntityAnaly.GetTableName<TEntity>();
            ExprAnaly.AnalySelectorExpression(selector, BuildSql.FormatName);
            string fieldName = ExprAnaly.GetValue();
            ISqlBuilder sqlBuilder = BuildFunction(tbName, fieldName);

            ISqlBuilder whereBuilder = CreateSqlBuilder();
            string exprSql = GetExprSql(whereBuilder);
            //whereBuilder.AddSQLText(exprSql);

            exprSql = MergeSqlBuilder(exprSql, whereBuilder, joinWhereBuilder); //2020.6.8 
            if (!string.IsNullOrEmpty(exprSql))
            {
                whereBuilder.AddSQLText(" {0}", exprSql);
            }

            BeforeExecSqlBuilder<TEntity>(whereBuilder, EmDbOperation.Read);
            if (whereBuilder.SQLTextLength > 2)
            {
                sqlBuilder.AddSQLText(" where {0}", whereBuilder.SQLText);
                sqlBuilder.AppendDbParam(whereBuilder.DbParam);
            }
            var result = new ScalarResult(DbHelper, sqlBuilder);
            return result;
        }
        /// <summary>
        /// 查询首行首列的基本方法,其它方法共通,(将 condition合并生成sql文本和参数)
        /// </summary>
        ScalarResult QueryFunction<TEntity>(Expression<Func<TEntity, object>> selector,
            IQueryCondition condition,
            Func<string, string, ISqlBuilder> BuildFunction,
            ISqlBuilder joinWhereBuilder = null
            ) where TEntity : class, new()
        {
            return
            QueryFunction<TEntity>(selector, (sqlBuilder) =>
            {
                string exprSql = WhereJoin<TEntity>(condition.WhereExpressions, sqlBuilder);
                return exprSql;

            }, BuildFunction, joinWhereBuilder);
        }
        /// <summary>
        /// 查询首行首列的基本方法,其它方法共通,(解析表达式sql文本和参数)
        /// </summary>
        ScalarResult QueryFunction<TEntity>(Expression<Func<TEntity, object>> selector, Expression<Func<TEntity, bool>> lamda, Func<string, string, ISqlBuilder> BuildFunction, ISqlBuilder joinWhereBuilder = null) where TEntity : class, new()
        {
            return
            QueryFunction<TEntity>(selector, (sqlBuilder) =>
            {
                ExprAnaly.AnalyWhereExpression(lamda, BuildSql.FormatName, sqlBuilder.AddParam);
                string exprSql = ExprAnaly.GetValue();
                return exprSql;

            }, BuildFunction, joinWhereBuilder);
        }
        /// <summary>
        /// 执行最大值函数查询
        /// </summary>
        public virtual ScalarResult QueryMax<TEntity>(Expression<Func<TEntity, object>> selector, Expression<Func<TEntity, bool>> lamda, ISqlBuilder joinWhereBuilder = null) where TEntity : class, new()
        {
            return QueryFunction(selector, lamda, BuildSql.BuildMaxFunction, joinWhereBuilder);
        }
        public virtual ScalarResult QueryMax<TEntity>(Expression<Func<TEntity, object>> selector, IQueryCondition condition, ISqlBuilder joinWhereBuilder = null) where TEntity : class, new()
        {
            return QueryFunction(selector, condition, BuildSql.BuildMaxFunction, joinWhereBuilder);
        }
        public virtual ScalarResult QueryMin<TEntity>(Expression<Func<TEntity, object>> selector, Expression<Func<TEntity, bool>> lamda, ISqlBuilder joinWhereBuilder = null) where TEntity : class, new()
        {
            return QueryFunction(selector, lamda, BuildSql.BuildMinFunction, joinWhereBuilder);
        }
        public virtual ScalarResult QueryMin<TEntity>(Expression<Func<TEntity, object>> selector, IQueryCondition condition, ISqlBuilder joinWhereBuilder = null) where TEntity : class, new()
        {
            return QueryFunction(selector, condition, BuildSql.BuildMinFunction, joinWhereBuilder);
        }
        public virtual ScalarResult QuerySum<TEntity>(Expression<Func<TEntity, object>> selector, Expression<Func<TEntity, bool>> lamda, ISqlBuilder joinWhereBuilder = null) where TEntity : class, new()
        {
            return QueryFunction(selector, lamda, BuildSql.BuildSumFunction, joinWhereBuilder);
        }
        public virtual ScalarResult QuerySum<TEntity>(Expression<Func<TEntity, object>> selector, IQueryCondition condition, ISqlBuilder joinWhereBuilder = null) where TEntity : class, new()
        {
            return QueryFunction(selector, condition, BuildSql.BuildSumFunction, joinWhereBuilder);
        }
        public virtual ScalarResult QueryCountResult<TEntity>(Expression<Func<TEntity, bool>> lamda, ISqlBuilder joinWhereBuilder = null) where TEntity : class, new()
        {
            var r =
            QueryFunction<TEntity>(null, lamda, (tbName, fields) =>
            {
                ISqlBuilder sqlBuilder = BuildSql.BuildCountFunction(tbName);
                return sqlBuilder;
            }, joinWhereBuilder);
            return r;
        }
        public int QueryCount<TEntity>(Expression<Func<TEntity, bool>> lamda, ISqlBuilder joinWhereBuilder = null) where TEntity : class, new()
        {
            var r = QueryCountResult<TEntity>(lamda, joinWhereBuilder);
            var count = r.ToObject<int>();
            return count;
        }
        public virtual ScalarResult QueryCountResult<TEntity>(IQueryCondition condition, ISqlBuilder joinWhereBuilder = null) where TEntity : class, new()
        {
            var r =
            QueryFunction<TEntity>(null, condition, (tbName, fields) =>
            {
                ISqlBuilder sqlBuilder = BuildSql.BuildCountFunction(tbName);
                return sqlBuilder;
            }, joinWhereBuilder);
            return r;
        }
        public int QueryCount<TEntity>(IQueryCondition condition, ISqlBuilder joinWhereBuilder = null) where TEntity : class, new()
        {
            var r = QueryCountResult<TEntity>(condition, joinWhereBuilder);
            var count = r.ToObject<int>();
            return count;
        }
        public bool QueryExist<TEntity>(IQueryCondition condition, ISqlBuilder joinWhereBuilder = null) where TEntity : class, new()
        {
            var r = QueryCount<TEntity>(condition, joinWhereBuilder);
            return r > 0;
        }
        public bool QueryExist<TEntity>(Expression<Func<TEntity, bool>> lamda, ISqlBuilder joinWhereBuilder = null) where TEntity : class, new()
        {
            var r = QueryCount<TEntity>(lamda, joinWhereBuilder);
            return r > 0;
        }
        /// <summary>
        /// 执行存储过程的方法
        /// </summary>
        /// <typeparam name="ProcEntity">存储过程实体类</typeparam>
        /// <param name="procObj">存储过程实体对象</param>
        /// <returns>返回存储过程结果</returns>
        public virtual ProcResult ExecProcedure<ProcEntity>(ProcEntity procObj) where ProcEntity : ProcEntityBase
        {
            return ExecProcedure(procObj, null);
        }
        /// <summary>
        /// 执行存储过程的方法, 附加动态输入参数
        /// </summary>
        /// <typeparam name="ProcEntity">存储过程实体类</typeparam>
        /// <param name="procObj">存储过程实体对象</param>
        /// <param name="otherArgs">附加动态输入参数</param>
        /// <returns>返回存储过程结果</returns>
        public virtual ProcResult ExecProcedure<ProcEntity>(ProcEntity procObj, Dictionary<string, object> otherArgs) where ProcEntity : ProcEntityBase
        {
            var r = new ProcResult(DbHelper, procObj, otherArgs);
            return r;
        }
        /// <summary>
        /// 按存储过程名称执行存储过程
        /// </summary>
        public virtual ProcResultBase ExecProcedure(string procName, Dictionary<string, object> args)
        {
            var r = new ProcResultBase(DbHelper, procName, args);
            return r;
        }
    }
}